# from connection import POOL
from backend.api.connection import POOL

def createTables():
    conn = POOL.connection()
    cur = conn.cursor()

    # 用户管理表
    sql = '''CREATE TABLE IF NOT EXISTS `zhifou_word`.`userinfo`  (
            `uid` int(10) NOT NULL AUTO_INCREMENT,
            `username` varchar(255) NULL,
            `password` varchar(255) NULL,
            `type` int(1) DEFAULT 0,
            `last_planid` int(10) NULL,
            PRIMARY KEY (`uid`)
            )CHARSET=utf8 AUTO_INCREMENT=10000;'''

    cur.execute(sql)
    conn.commit()

    # 词书管理表
    sql = '''CREATE TABLE IF NOT EXISTS `zhifou_word`.`booklist`  (
            `bid` int(10) NOT NULL AUTO_INCREMENT,
            `bookname` varchar(255) NULL,
            `creator` int(10) NULL,
            `total_num` int(10) NULL,
            PRIMARY KEY (`bid`)
            )CHARSET=utf8;'''
    cur.execute(sql)
    conn.commit()

    # 计划管理表
    sql = '''CREATE TABLE IF NOT EXISTS `zhifou_word`.`planlist`  (
            `pid` int(10) NOT NULL AUTO_INCREMENT,
            `bid` int(10) NULL,
            `bookname` varchar(255) NULL,
            `uid` int(10) NULL,
            `create_time` timestamp(0) NULL,
            `edit_time` timestamp(0) NULL,
            `check_in_info` varchar(20000) NULL,
            `total_num` int(10) NULL,
            `remain_num` int(10) NULL,
            `everyday_num` int(10) NULL,
            `day_remain_num` int(10) NULL,
            PRIMARY KEY (`pid`)
            )CHARSET=utf8;'''
    cur.execute(sql)
    conn.commit()

    # 题库表
    sql = '''CREATE TABLE IF NOT EXISTS `zhifou_word`.`exerciselist`  (
            `exid` int(10) NOT NULL AUTO_INCREMENT,
            `word` varchar(255) NULL,
            `question` varchar(255) NULL,
            `choice_A` varchar(255) NULL,
            `choice_B` varchar(255) NULL,
            `choice_C` varchar(255) NULL,
            `choice_D` varchar(255) NULL,
            `answer` varchar(255) NULL,
            `creator` int(10) NULL,
            `create_time` timestamp(0) NULL,
            `edit_time` timestamp(0) NULL,
            `approve` int(1) NULL,
            PRIMARY KEY (`exid`)
            )CHARSET=utf8;'''
    cur.execute(sql)
    conn.commit()

    # 笔记表
    sql = '''CREATE TABLE IF NOT EXISTS `zhifou_word`.`notelist`  (
            `nid` int(10) NOT NULL AUTO_INCREMENT,
            `word` varchar(255) NULL,
            `content` varchar(255) NULL,
            `creator` int(10) NULL,
            `create_time` timestamp(0) NULL,
            `edit_time` timestamp(0) NULL,
            `approve` int(1) NULL,
            PRIMARY KEY (`nid`)
            )CHARSET=utf8;'''
    cur.execute(sql)
    conn.commit()
    # data = cur.fetchall()
    # response = {}
    # for item in data:
    #     pass

    cur.close()

    # return response


if __name__ == "__main__":
    createTables()
